﻿CREATE FUNCTION [dbo].StateFlowToByGroup(
	@principal_id sysname,
	@StateFromID int
)RETURNS TABLE AS

RETURN(
SELECT DISTINCT TOP (100) PERCENT
	wf.StateToID, wf.NameTo, CONVERT(bit, ISNULL(States.StateToID, 0)) AS HasAccess,
	wf.Dir AS Direction,wf.Dir, wf.StateToPos, wf.StateFlowPosTo
FROM  v_StateFlow AS wf
LEFT OUTER JOIN 
(	SELECT wf.StateToID FROM v_StateFlow AS wf
	INNER JOIN t_StateFlow_Groups AS wfg
		ON wf.StateFromID = wfg.StateFromID AND
			 wf.StateToID = wfg.StateToID AND
       wfg.principal_id = @principal_id
  WHERE (wf.StateFromID = @StateFromID)
)States ON States.StateToID = wf.StateToID
WHERE (wf.StateFromID = @StateFromID)
ORDER BY wf.StateToPos)
